import { createPool } from 'mysql2/promise'

// 存储连接池的 Map
const poolMap = new Map()

/**
 * 创建 MySQL 连接池
 * @param {string} poolId 连接池ID
 * @param {object} config 数据库配置
 * @returns {Promise<string>} 返回连接池ID
 */
export const createMysqlPool = async (poolId, config) => {
    try {
        // 如果连接池已存在，先关闭旧的
        if (poolMap.has(poolId)) {
            await poolMap.get(poolId).end()
        }

        const pool = createPool({
            host: config.host,
            port: config.port || 3306,
            user: config.user,
            password: config.password,
            database: config.database,
            waitForConnections: true,
            connectionLimit: config.connectionLimit || 10,
            queueLimit: 0,
            acquireTimeout: config.acquireTimeout || 60000,
            timeout: config.timeout || 60000,
            reconnect: true,
            charset: config.charset || 'utf8mb4'
        })

        // 测试连接
        const connection = await pool.getConnection()
        await connection.ping()
        connection.release()

        poolMap.set(poolId, pool)
        console.log(`MySQL连接池创建成功: ${config.host}:${config.port}`)
        return poolId
    } catch (err) {
        console.error(`创建MySQL连接池失败: ${config.host}:${config.port}`, err)
        throw new Error(`创建MySQL连接池失败: ${config.host}:${config.port} - ${err.message}`)
    }
}

/**
 * 执行 MySQL 查询
 * @param {string} poolId 连接池ID
 * @param {string} sql SQL语句
 * @param {array} params 参数数组
 * @returns {Promise<object>} 查询结果
 */
export const executeMysqlQuery = async (poolId, sql, params = []) => {
    try {
        const pool = poolMap.get(poolId)
        if (!pool) {
            throw new Error(`连接池不存在: ${poolId}`)
        }

        const [rows, fields] = await pool.execute(sql, params)
        return {
            success: true,
            data: rows,
            fields: fields?.map(field => ({
                name: field.name,
                type: field.type,
                length: field.length
            })) || [],
            rowCount: Array.isArray(rows) ? rows.length : 0
        }
    } catch (err) {
        console.error(`MySQL查询执行失败: ${poolId}`, err)
        return {
            success: false,
            error: err.message,
            data: [],
            fields: [],
            rowCount: 0
        }
    }
}

/**
 * 批量执行 MySQL 查询
 * @param {string} poolId 连接池ID
 * @param {array} queries 查询数组 [{sql, params}, ...]
 * @returns {Promise<array>} 查询结果数组
 */
export const executeMysqlBatchQuery = async (poolId, queries) => {
    const results = []

    for (const query of queries) {
        const result = await executeMysqlQuery(poolId, query.sql, query.params)
        results.push(result)
    }

    return results
}

/**
 * 执行 MySQL 事务
 * @param {string} poolId 连接池ID
 * @param {array} queries 查询数组 [{sql, params}, ...]
 * @returns {Promise<object>} 事务执行结果
 */
export const executeMysqlTransaction = async (poolId, queries) => {
    const pool = poolMap.get(poolId)
    if (!pool) {
        throw new Error(`连接池不存在: ${poolId}`)
    }

    const connection = await pool.getConnection()

    try {
        await connection.beginTransaction()

        const results = []
        for (const query of queries) {
            const [rows, fields] = await connection.execute(query.sql, query.params || [])
            results.push({
                success: true,
                data: rows,
                fields: fields?.map(field => ({
                    name: field.name,
                    type: field.type,
                    length: field.length
                })) || [],
                rowCount: Array.isArray(rows) ? rows.length : 0
            })
        }

        await connection.commit()
        return {
            success: true,
            results: results
        }
    } catch (err) {
        await connection.rollback()
        console.error(`MySQL事务执行失败: ${poolId}`, err)
        return {
            success: false,
            error: err.message,
            results: []
        }
    } finally {
        connection.release()
    }
}

/**
 * 关闭 MySQL 连接池
 * @param {string} poolId 连接池ID
 * @returns {Promise<boolean>} 是否成功关闭
 */
export const closeMysqlPool = async (poolId) => {
    try {
        const pool = poolMap.get(poolId)
        if (pool) {
            await pool.end()
            poolMap.delete(poolId)
            console.log(`MySQL连接池已关闭: ${poolId}`)
        }
        return true
    } catch (err) {
        console.error(`关闭MySQL连接池失败: ${poolId}`, err)
        return false
    }
}

/**
 * 关闭所有 MySQL 连接池
 */
export const closeAllMysqlPools = async () => {
    const closePromises = Array.from(poolMap.keys()).map(poolId => closeMysqlPool(poolId))
    await Promise.all(closePromises)
}

/**
 * 获取连接池状态
 * @param {string} poolId 连接池ID
 * @returns {object} 连接池状态信息
 */
export const getMysqlPoolStatus = (poolId) => {
    const pool = poolMap.get(poolId)
    if (!pool) {
        return { exists: false }
    }

    return {
        exists: true,
        config: {
            connectionLimit: pool.config.connectionLimit,
            acquireTimeout: pool.config.acquireTimeout,
            timeout: pool.config.timeout
        },
        pool: {
            allConnections: pool.pool.allConnections.length,
            freeConnections: pool.pool.freeConnections.length,
            queuedConnectionRequests: pool.pool.queuedConnectionRequests.length
        }
    }
}